"""
    本文件用于：Bug详情页的所有SQL语句
"""


def construct_sql_Bug_statistics_detail_old(
        _tag_type, _Bug_type, _subsystem, _milestone_verison, _verison_NO, _tester_name, _date_range, _resolution, _ids=None
):
    return f"""
SELECT
		bug.id as bug_id, bug.title as title, bug.`status` as `status`, bug.pri as priority, usr.realname as creator, bug.resolution as resolution
		, task.`name` as task_name, task.id as task_id, pject.milestone, prod.`name` as prod_name, prod.id as prod_id, bug.openedDate as create_date
FROM
		zentao.zt_bug as bug
LEFT JOIN
		zentao.zt_user as usr
ON
		usr.account = bug.openedBy
LEFT JOIN
		zentao.zt_project as pject 
ON
		pject.id = bug.project
LEFT JOIN
		zentao.zt_testtask as task 
ON
		task.project = pject.id
LEFT JOIN
		zentao.zt_product as prod 
ON
		prod.id=bug.product
WHERE
		bug.deleted = '0'
{
'''AND
		usr.realname = '%s' # 姓名''' % _tester_name if _tester_name else ''
}
{
'''AND
		bug.product = %d # 子系统''' % _subsystem if type(_subsystem) is int else ''
}
{
'''AND
		pject.milestone = '%s' # 版本类型''' % _milestone_verison if _milestone_verison else ''
}
{
'''AND
		task.`name` = '%s' # 版本号''' % _verison_NO if _verison_NO else ''
}
{
'''AND
		bug.openedDate > DATE_SUB(CURRENT_DATE, INTERVAL %d day) # 时间范围
AND
		bug.openedDate <= CURRENT_DATE'''  % _date_range if type(_date_range) is int else ''
}
{
'''AND
		bug.`status` = '%s' # Bug状态标签''' % _Bug_type if _Bug_type else ''
}
{
'''AND
        bug.resolution = '%s'  # bug解决方案''' % _resolution if _resolution else ''
}
{
'''AND
		bug.id = '%d' # id
	group by 
	    bug.id''' % _ids if _ids and len(_ids) == 1 else (
    ''  if not _ids 
        else 
f'''AND
		bug.id in {_ids} # id
    group by 
	    bug.id		
		''')
}
limit 20
"""


# 查询Bug统计页的bug清单
def construct_sql_Bug_statistics_detail(
        _project_id,
        _subsys_id,
        _creator_name,
        _assigner_name,
        _date_create_start,
        _date_create_end,
        _date_solve_start,
        _date_solve_end,
        _sovle_resolution,
        _Bug_status,
        _pageNo=0,
        _pageNum=25,
        _ids=None):
    return f"""
select
		pject.id as project_id,
		pj.id as subsys_id,
		pject.name as project_name,
		pj.name as subsys_name,
		bug.id as bug_id,
		bug.severity as severity,
		bug.title as title,
		usr.realname as creator,
		bug.openedDate as create_date,
		if(bug.status = 'closed', 'closed', usrB.realname) as assigner,
		bug.resolvedDate as sovled_date,
		bug.`status` as status,
		bug.resolution as resolution
FROM
		zt_bug as bug 
LEFT JOIN
		zt_project as pj 
ON
		pj.id=bug.project
LEFT JOIN 
		zt_project as pject 
ON
		pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
LEFT JOIN
		zt_user as usr 
ON
		usr.account=bug.openedBy
LEFT JOIN
		zt_user as usrB 
ON
		usrB.account=bug.assignedTo
WHERE
		bug.deleted='0'
{
'''AND
		usr.realname = '%s' # Bug创建人''' % _creator_name if _creator_name else ''
}
{
'''AND
		usrB.realname = '%s' # Bug指派人''' % _assigner_name if _assigner_name else ''
}
{
'''AND
		pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id > 0 else 
            '''AND
                bug.project = %d # 项目id''' % _project_id if _project_id == 0
        else ''
}
{
'''AND
		pj.id = %d # 项目id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
}
{
f'''
		{"AND bug.openedDate > '%s'" % _date_create_start if _date_create_start else ''} # 创建的时间
		{"AND bug.openedDate <= '%s'" % _date_create_end if _date_create_end else ''}'''
}
{
f'''
		{"AND bug.resolvedDate > '%s'" % _date_solve_start if _date_solve_start else ''} # 解决Bug的时间
		{"AND bug.resolvedDate <= '%s'" % _date_solve_end if _date_solve_end else ''}'''
}
{
'''AND
		bug.`resolution` = '%s' # Bug解决状态''' % _sovle_resolution if _sovle_resolution else ''
}
{
'''AND
		bug.`status` = '%s' # Bug状态标签''' % _Bug_status if _Bug_status else ''
}
{
'''AND
		bug.id = '%d' # id
	group by 
	    bug.id''' % _ids if _ids and len(_ids) == 1 else (
    ''  if not _ids 
        else 
f'''AND
		bug.id in {_ids} # id
    group by 
	    bug.id		
		''')
}
order by 
        bug.id
desc
limit {_pageNo * _pageNum}, {_pageNum}
"""


# 统计所有数据
def construct_sql_Bug_statistics_all(
        _project_id,
        _subsys_id,
        _creator_name,
        _assigner_name,
        _date_create_start,
        _date_create_end,
        _date_solve_start,
        _date_solve_end,
        _sovle_resolution,
        _Bug_status):
    return f"""
select 
        count(id) as total
from 
        (
        select
                bug.id
        FROM
                zt_bug as bug 
        LEFT JOIN
                zt_project as pj 
        ON
                pj.id=bug.project
        LEFT JOIN
                zt_project as pject 
        ON
                pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
        LEFT JOIN
                zt_user as usr 
        ON
                usr.account=bug.openedBy
        LEFT JOIN
                zt_user as usrB 
        ON
                usrB.account=bug.assignedTo
        WHERE
                bug.deleted='0'
        {
            '''AND
                usr.realname = '%s' # Bug创建人''' % _creator_name if _creator_name else ''
            }
        {
            '''AND
                usrB.realname = '%s' # Bug指派人''' % _assigner_name if _assigner_name else ''
            }
        {
            '''AND
                pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id > 0 else 
            '''AND
                bug.project = %d # 项目id''' % _project_id if _project_id == 0
        else ''
            }
        {
            '''AND
                    pj.id = %d # 项目id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
            }
        {
            f'''
                {"AND bug.openedDate > '%s'" % _date_create_start if _date_create_start else ''} # 创建的时间
                {"AND bug.openedDate <= '%s'" % _date_create_end if _date_create_end else ''}'''
            }
        {
            f'''
                {"AND bug.resolvedDate > '%s'" % _date_solve_start if _date_solve_start else ''} # 解决Bug的时间
                {"AND bug.resolvedDate <= '%s'" % _date_solve_end if _date_solve_end else ''}'''
            }
        {
            '''AND
                    bug.`resolution` = '%s' # Bug解决状态''' % _sovle_resolution if _sovle_resolution else ''
            }
        {
            '''AND
                bug.`status` = '%s' # Bug状态标签''' % _Bug_status if _Bug_status else ''
            }
        ) as new
"""





case_creator_create_sql = """
SELECT
        usr.realname as name, count(1) as count
FROM
        zentao.zt_case as cas
LEFT JOIN
        zentao.zt_user as usr
ON
        usr.account = cas.openedBy
GROUP BY 
        cas.openedBy
ORDER BY
      count
desc


"""



# Bug创建人名单
Bug_tester_name_list_sql = """
SELECT
		usr.realname as name
FROM
		zentao.zt_bug as bug
LEFT JOIN
		zentao.zt_user as usr
ON
		usr.account=bug.openedBy
WHERE
		usr.deleted='0'
AND
        usr.realname != 'admin'
GROUP BY
		bug.openedBy
"""


def construct_sql_Bug_version_by_subsys_search_sql(_subsys_id=None):
    return  f"""
select 
        id, name 
from
    zt_project as proj
where
    proj.type='sprint'
and 
    proj.deleted = '0'
{
    f'and proj.parent={_subsys_id}' if _subsys_id 
    else ''
}
;"""


# 查询所有的子系统
def construct_sql_Bug_subsys_by_project_search_sql(_project_id=None):
    return f"""
select 
        proj.id as id, 
        proj.name as name
from
        zt_project as proj   
where
        proj.type='project'
and
        proj.parent > 0
and
        proj.deleted = '0'
{
    f'and proj.parent={_project_id}' if _project_id 
    else ''
}
"""



Bug_all_project_name_list_sql = """
select 
		id, `name`, `status`
FROM
		zt_project
WHERE
		type in ('program', 'project')
AND
		project=0
AND
        parent=0
AND
		deleted='0'

;"""


# Bug创建人名单
Bug_creator_name_list_sql = """
SELECT
		usr.realname as name
FROM
		zentao.zt_bug as bug
LEFT JOIN
		zentao.zt_user as usr
ON
		usr.account=bug.openedBy
WHERE
		usr.deleted='0'
AND
        usr.realname != 'admin'
GROUP BY
		bug.openedBy
"""



# Bug指派人名单
Bug_assigner_name_list_sql = """
SELECT
		usr.realname as name
FROM
		zentao.zt_bug as bug
LEFT JOIN
		zentao.zt_user as usr
ON
		usr.account=bug.assignedTo
WHERE
		usr.deleted='0'
AND
        usr.realname != 'admin'
GROUP BY
		bug.assignedTo
;"""




def construct_sql_Bug_create_all_with_detail(
    _date_bug_create_start,
    _date_bug_create_end
):
    return f"""
(
    select
        " " as creator, 
        "【总数】" as week_day,
        count(IF(WEEKDAY(bug.openedDate)=0,1,null)) as  Monday, 
        count(IF(WEEKDAY(bug.openedDate)=1,1,null)) as	Tuesday, 
        count(IF(WEEKDAY(bug.openedDate)=2,1,null)) as	Wednesday, 
        count(IF(WEEKDAY(bug.openedDate)=3,1,null)) as	Thursday, 
        count(IF(WEEKDAY(bug.openedDate)=4,1,null)) as	Friday, 
        count(IF(WEEKDAY(bug.openedDate)=5,1,null)) as	Saturday,
        count(IF(WEEKDAY(bug.openedDate)=6,1,null)) as	Sunday, 
        count(bug.id) as total
    FROM
        zt_bug as bug 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=bug.project
    LEFT JOIN
        zt_project as pject 
    ON
        pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,",",2),",",-1) 
    LEFT JOIN
        zt_user as usr
    ON
        usr.account=bug.openedBy 
    LEFT JOIN
        zt_user as usrB 
    ON
        usrB.account=bug.assignedTo 
    WHERE
        bug.deleted="0" 
    and 
        pject.status != 'closed'
    {
    '''AND
        bug.openedDate >= "%s" ''' % _date_bug_create_start if _date_bug_create_start else ""
    }
    {
    '''AND
        bug.openedDate < "%s" ''' % _date_bug_create_end if _date_bug_create_end else "" 
    }
)

UNION

(
    select
        usr.realname as creator, 
        WEEKDAY(bug.openedDate)+1 as week_day,
        count(IF(WEEKDAY(bug.openedDate)=0,1,null)) as  Monday, 
        count(IF(WEEKDAY(bug.openedDate)=1,1,null)) as	Tuesday, 
        count(IF(WEEKDAY(bug.openedDate)=2,1,null)) as	Wednesday, 
        count(IF(WEEKDAY(bug.openedDate)=3,1,null)) as	Thursday, 
        count(IF(WEEKDAY(bug.openedDate)=4,1,null)) as	Friday, 
        count(IF(WEEKDAY(bug.openedDate)=5,1,null)) as	Saturday,
        count(IF(WEEKDAY(bug.openedDate)=6,1,null)) as	Sunday, 
        count(bug.id) as "创建Bug总数"
    FROM
        zt_bug as bug 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=bug.project
    LEFT JOIN
        zt_project as pject 
    ON
        pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,",",2),",",-1) 
    LEFT JOIN
        zt_user as usr
    ON
        usr.account=bug.openedBy 
    LEFT JOIN
        zt_user as usrB 
    ON
        usrB.account=bug.assignedTo 
    WHERE
        bug.deleted="0" 
    and 
        pject.status != 'closed'
    {
    '''AND
        bug.openedDate >= "%s" ''' % _date_bug_create_start if _date_bug_create_start else ""
    }
    {
    '''AND
        bug.openedDate < "%s" ''' % _date_bug_create_end if _date_bug_create_end else "" 
    }
    GROUP BY
        bug.openedBy
)

"""



def construct_sql_Bug_verify_all_with_detail(
        _date_bug_verify_start,
        _date_bug_verify_end
):
    return f"""
(
    select
        " " as actor, 
        "【总数】" as week_day,
        count(distinct IF(WEEKDAY(act.date)=0,bug.id,null)) as  Monday,
        count(distinct IF(WEEKDAY(act.date)=1,bug.id,null)) as	Tuesday,
        count(distinct IF(WEEKDAY(act.date)=2,bug.id,null)) as	Wednesday,
        count(distinct IF(WEEKDAY(act.date)=3,bug.id,null)) as	Thursday,
        count(distinct IF(WEEKDAY(act.date)=4,bug.id,null)) as	Friday,
        count(distinct IF(WEEKDAY(act.date)=5,bug.id,null)) as	Saturday,
        count(distinct IF(WEEKDAY(act.date)=6,bug.id,null)) as	Sunday,
        count(distinct bug.id) as total
    from
        zt_action as act
    left join
        zt_bug as bug
    on
        bug.id=act.objectID
    left join
        zt_user as usr
    on
        usr.account=act.actor
    left join
        zt_user as usrB
    on
        usrB.account = bug.openedBy
    where
        act.action='edited'
    and
        objectType='bug'
    and
        bug.status = 'active'
    and 
        pject.status != 'closed'
    and
        act.comment != ''
        {
        '''AND
            act.date >= "%s" ''' % _date_bug_verify_start if _date_bug_verify_start else ""
        }
        {
        '''AND
            act.date < "%s" ''' % _date_bug_verify_end if _date_bug_verify_end else "" 
        }
    and
        bug.openedBy = act.actor
)

UNION

(
    select
        usr.realname as actor,
        WEEKDAY(act.date)+1 as week_day,
        count(distinct IF(WEEKDAY(act.date)=0,bug.id,null)) as  Monday,
        count(distinct IF(WEEKDAY(act.date)=1,bug.id,null)) as	Tuesday,
        count(distinct IF(WEEKDAY(act.date)=2,bug.id,null)) as	Wednesday,
        count(distinct IF(WEEKDAY(act.date)=3,bug.id,null)) as	Thursday,
        count(distinct IF(WEEKDAY(act.date)=4,bug.id,null)) as	Friday,
        count(distinct IF(WEEKDAY(act.date)=5,bug.id,null)) as	Saturday,
        count(distinct IF(WEEKDAY(act.date)=6,bug.id,null)) as	Sunday,
        count(distinct bug.id) as total
    from
        zt_action as act
    left join
        zt_bug as bug
    on
        bug.id=act.objectID
    left join
        zt_user as usr
    on
        usr.account=act.actor
    left join
        zt_user as usrB
    on
        usrB.account = bug.openedBy
    where
        act.action='edited'
    and
        objectType='bug'
    and
        bug.status = 'active'
    and 
        pject.status != 'closed'
    and
        act.comment != ''
        {
        '''AND
            act.date >= "%s" ''' % _date_bug_verify_start if _date_bug_verify_start else ""
        }
        {
        '''AND
            act.date < "%s" ''' % _date_bug_verify_end if _date_bug_verify_end else "" 
        }
    and
        bug.openedBy = act.actor
    group by
        act.actor
)
"""




# Bug关闭
def construct_sql_Bug_close_all_with_detail(
        _date_bug_close_start,
        _date_bug_close_end
):
    return f"""
(
    select
        " " as closer, 
        "【总数】" as week_day,
        count(IF(WEEKDAY(bug.closedDate)=0,1,null)) as  Monday, 
        count(IF(WEEKDAY(bug.closedDate)=1,1,null)) as	Tuesday, 
        count(IF(WEEKDAY(bug.closedDate)=2,1,null)) as	Wednesday, 
        count(IF(WEEKDAY(bug.closedDate)=3,1,null)) as	Thursday, 
        count(IF(WEEKDAY(bug.closedDate)=4,1,null)) as	Friday, 
        count(IF(WEEKDAY(bug.closedDate)=5,1,null)) as	Saturday,
        count(IF(WEEKDAY(bug.closedDate)=6,1,null)) as	Sunday, 
        count(bug.id) as total 
    FROM
        zt_bug as bug 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=bug.project
    LEFT JOIN
        zt_project as pject 
    ON
        pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,",",2),",",-1) 
    LEFT JOIN
        zt_user as usr
    ON
        usr.account=bug.openedBy 
    LEFT JOIN
        zt_user as usrB 
    ON
        usrB.account=bug.assignedTo 
    WHERE
        bug.deleted="0"
    and 
        bug.status='closed' 
    and 
        pject.status != 'closed'
    {
    '''AND
        bug.closedDate >= "%s" ''' % _date_bug_close_start if _date_bug_close_start else ""
    }
    {
    '''AND
        bug.closedDate < "%s" ''' % _date_bug_close_end if _date_bug_close_end else "" 
    }
)

UNION

(
    SELECT
        usr.realname as closer,
        Weekday(bug.closedDate)+1 as week_day,
        count(IF(WEEKDAY(bug.closedDate)=0,1,null)) as Monday, 
        count(IF(WEEKDAY(bug.closedDate)=1,1,null)) as	Tuesday, 
        count(IF(WEEKDAY(bug.closedDate)=2,1,null)) as	Wednesday, 
        count(IF(WEEKDAY(bug.closedDate)=3,1,null)) as	Thursday, 
        count(IF(WEEKDAY(bug.closedDate)=4,1,null)) as	Friday, 
        count(IF(WEEKDAY(bug.closedDate)=5,1,null)) as	Saturday,
        count(IF(WEEKDAY(bug.closedDate)=6,1,null)) as	Sunday, 
        count(bug.id) as total
    FROM
        zt_bug as bug 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id = bug.project 
    LEFT JOIN
        zt_project as pject 
    ON
        pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path, ',', 2),',', -1)
    LEFT JOIN
        zt_user as usr 
    ON
        usr.account = bug.closedBy
    WHERE
        bug.deleted='0'
    and 
        bug.status='closed'
    and 
        pject.status != 'closed'
    {
    '''AND
        bug.closedDate >= "%s" ''' % _date_bug_close_start if _date_bug_close_start else ""
    }
    {
    '''AND
        bug.closedDate < "%s" ''' % _date_bug_close_end if _date_bug_close_end else "" 
    }
    GROUP BY
        bug.closedBy
)
"""



def construct_sql_Bug_create_all_with_detail_by_date(
        _date_bug_create_start,
        _date_bug_create_end
):
    return f"""
    (
        select
            "【总数】" as creator, 
            count(if(Day(bug.openedDate) >= 1  and Day(bug.openedDate) < 2, 1 , null)) as "1",
            count(if(Day(bug.openedDate) >= 2  and Day(bug.openedDate) < 3, 1 , null)) as "2",
            count(if(Day(bug.openedDate) >= 3  and Day(bug.openedDate) < 4, 1 , null)) as "3",
            count(if(Day(bug.openedDate) >= 4  and Day(bug.openedDate) < 5, 1 , null)) as "4",
            count(if(Day(bug.openedDate) >= 5  and Day(bug.openedDate) < 6, 1 , null)) as "5",
            count(if(Day(bug.openedDate) >= 6  and Day(bug.openedDate) < 7, 1 , null)) as "6",
            count(if(Day(bug.openedDate) >= 7  and Day(bug.openedDate) < 8, 1 , null)) as "7",
            count(if(Day(bug.openedDate) >= 8  and Day(bug.openedDate) < 9, 1 , null)) as "8",
            count(if(Day(bug.openedDate) >= 9  and Day(bug.openedDate) < 10, 1 , null)) as "9",
            count(if(Day(bug.openedDate) >= 10 and Day(bug.openedDate) < 11, 1 , null)) as "10",
            count(if(Day(bug.openedDate) >= 11 and Day(bug.openedDate) < 12, 1 , null)) as "11",
            count(if(Day(bug.openedDate) >= 12 and Day(bug.openedDate) < 13, 1 , null)) as "12",
            count(if(Day(bug.openedDate) >= 13 and Day(bug.openedDate) < 14, 1 , null)) as "13",
            count(if(Day(bug.openedDate) >= 14 and Day(bug.openedDate) < 15, 1 , null)) as "14",
            count(if(Day(bug.openedDate) >= 15 and Day(bug.openedDate) < 16, 1 , null)) as "15",
            count(if(Day(bug.openedDate) >= 16 and Day(bug.openedDate) < 17, 1 , null)) as "16",
            count(if(Day(bug.openedDate) >= 17 and Day(bug.openedDate) < 18, 1 , null)) as "17",
            count(if(Day(bug.openedDate) >= 18 and Day(bug.openedDate) < 19, 1 , null)) as "18",
            count(if(Day(bug.openedDate) >= 19 and Day(bug.openedDate) < 20, 1 , null)) as "19",
            count(if(Day(bug.openedDate) >= 20 and Day(bug.openedDate) < 21, 1 , null)) as "20",
            count(if(Day(bug.openedDate) >= 21 and Day(bug.openedDate) < 22, 1 , null)) as "21",
            count(if(Day(bug.openedDate) >= 22 and Day(bug.openedDate) < 23, 1 , null)) as "22",
            count(if(Day(bug.openedDate) >= 23 and Day(bug.openedDate) < 24, 1 , null)) as "23",
            count(if(Day(bug.openedDate) >= 24 and Day(bug.openedDate) < 25, 1 , null)) as "24",
            count(if(Day(bug.openedDate) >= 25 and Day(bug.openedDate) < 26, 1 , null)) as "25",
            count(if(Day(bug.openedDate) >= 26 and Day(bug.openedDate) < 27, 1 , null)) as "26",
            count(if(Day(bug.openedDate) >= 27 and Day(bug.openedDate) < 28, 1 , null)) as "27",
            count(if(Day(bug.openedDate) >= 28 and Day(bug.openedDate) < 29, 1 , null)) as "28",
            count(if(Day(bug.openedDate) >= 29 and Day(bug.openedDate) < 30, 1 , null)) as "29",
            count(if(Day(bug.openedDate) >= 30 and Day(bug.openedDate) < 31, 1 , null)) as "30",
            count(if(Day(bug.openedDate) = 31, 1 , null)) as "31",
            count(bug.id) as total
        FROM
            zt_bug as bug 
        LEFT JOIN
            zt_project as pj 
        ON
            pj.id=bug.project
        LEFT JOIN
            zt_project as pject 
        ON
            pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,",",2),",",-1) 
        LEFT JOIN
            zt_user as usr
        ON
            usr.account=bug.openedBy 
        LEFT JOIN
            zt_user as usrB 
        ON
            usrB.account=bug.assignedTo 
        WHERE
            bug.deleted="0" 
        {
    '''AND
        bug.openedDate >= "%s" ''' % _date_bug_create_start if _date_bug_create_start else ""
    }
        {
    '''AND
        bug.openedDate < "%s" ''' % _date_bug_create_end if _date_bug_create_end else ""
    }
    )

    UNION

    (
        select
            usr.realname as creator, 
            count(if(Day(bug.openedDate) >= 1  and Day(bug.openedDate) < 2, 1 , null)) as "1",
            count(if(Day(bug.openedDate) >= 2  and Day(bug.openedDate) < 3, 1 , null)) as "2",
            count(if(Day(bug.openedDate) >= 3  and Day(bug.openedDate) < 4, 1 , null)) as "3",
            count(if(Day(bug.openedDate) >= 4  and Day(bug.openedDate) < 5, 1 , null)) as "4",
            count(if(Day(bug.openedDate) >= 5  and Day(bug.openedDate) < 6, 1 , null)) as "5",
            count(if(Day(bug.openedDate) >= 6  and Day(bug.openedDate) < 7, 1 , null)) as "6",
            count(if(Day(bug.openedDate) >= 7  and Day(bug.openedDate) < 8, 1 , null)) as "7",
            count(if(Day(bug.openedDate) >= 8  and Day(bug.openedDate) < 9, 1 , null)) as "8",
            count(if(Day(bug.openedDate) >= 9  and Day(bug.openedDate) < 10, 1 , null)) as "9",
            count(if(Day(bug.openedDate) >= 10 and Day(bug.openedDate) < 11, 1 , null)) as "10",
            count(if(Day(bug.openedDate) >= 11 and Day(bug.openedDate) < 12, 1 , null)) as "11",
            count(if(Day(bug.openedDate) >= 12 and Day(bug.openedDate) < 13, 1 , null)) as "12",
            count(if(Day(bug.openedDate) >= 13 and Day(bug.openedDate) < 14, 1 , null)) as "13",
            count(if(Day(bug.openedDate) >= 14 and Day(bug.openedDate) < 15, 1 , null)) as "14",
            count(if(Day(bug.openedDate) >= 15 and Day(bug.openedDate) < 16, 1 , null)) as "15",
            count(if(Day(bug.openedDate) >= 16 and Day(bug.openedDate) < 17, 1 , null)) as "16",
            count(if(Day(bug.openedDate) >= 17 and Day(bug.openedDate) < 18, 1 , null)) as "17",
            count(if(Day(bug.openedDate) >= 18 and Day(bug.openedDate) < 19, 1 , null)) as "18",
            count(if(Day(bug.openedDate) >= 19 and Day(bug.openedDate) < 20, 1 , null)) as "19",
            count(if(Day(bug.openedDate) >= 20 and Day(bug.openedDate) < 21, 1 , null)) as "20",
            count(if(Day(bug.openedDate) >= 21 and Day(bug.openedDate) < 22, 1 , null)) as "21",
            count(if(Day(bug.openedDate) >= 22 and Day(bug.openedDate) < 23, 1 , null)) as "22",
            count(if(Day(bug.openedDate) >= 23 and Day(bug.openedDate) < 24, 1 , null)) as "23",
            count(if(Day(bug.openedDate) >= 24 and Day(bug.openedDate) < 25, 1 , null)) as "24",
            count(if(Day(bug.openedDate) >= 25 and Day(bug.openedDate) < 26, 1 , null)) as "25",
            count(if(Day(bug.openedDate) >= 26 and Day(bug.openedDate) < 27, 1 , null)) as "26",
            count(if(Day(bug.openedDate) >= 27 and Day(bug.openedDate) < 28, 1 , null)) as "27",
            count(if(Day(bug.openedDate) >= 28 and Day(bug.openedDate) < 29, 1 , null)) as "28",
            count(if(Day(bug.openedDate) >= 29 and Day(bug.openedDate) < 30, 1 , null)) as "29",
            count(if(Day(bug.openedDate) >= 30 and Day(bug.openedDate) < 31, 1 , null)) as "30",
            count(if(Day(bug.openedDate) = 31, 1 , null)) as "31",
            count(bug.id) as "创建Bug总数"
        FROM
            zt_bug as bug 
        LEFT JOIN
            zt_project as pj 
        ON
            pj.id=bug.project
        LEFT JOIN
            zt_project as pject 
        ON
            pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,",",2),",",-1) 
        LEFT JOIN
            zt_user as usr
        ON
            usr.account=bug.openedBy 
        LEFT JOIN
            zt_user as usrB 
        ON
            usrB.account=bug.assignedTo 
        WHERE
            bug.deleted="0" 
        {
    '''AND
        bug.openedDate >= "%s" ''' % _date_bug_create_start if _date_bug_create_start else ""
    }
        {
    '''AND
        bug.openedDate < "%s" ''' % _date_bug_create_end if _date_bug_create_end else ""
    }
        GROUP BY
            bug.openedBy
    )

    """



def construct_sql_Bug_verify_all_with_detail_by_date(
        _date_bug_verify_start,
        _date_bug_verify_end
):
    return f"""
(
    select
        "【总数】" as actor,
        count(if(Day(act.date) >= 1  and Day(act.date) < 2, 1 , null)) as "1",
        count(if(Day(act.date) >= 2  and Day(act.date) < 3, 1 , null)) as "2",
        count(if(Day(act.date) >= 3  and Day(act.date) < 4, 1 , null)) as "3",
        count(if(Day(act.date) >= 4  and Day(act.date) < 5, 1 , null)) as "4",
        count(if(Day(act.date) >= 5  and Day(act.date) < 6, 1 , null)) as "5",
        count(if(Day(act.date) >= 6  and Day(act.date) < 7, 1 , null)) as "6",
        count(if(Day(act.date) >= 7  and Day(act.date) < 8, 1 , null)) as "7",
        count(if(Day(act.date) >= 8  and Day(act.date) < 9, 1 , null)) as "8",
        count(if(Day(act.date) >= 9  and Day(act.date) < 10, 1 , null)) as "9",
        count(if(Day(act.date) >= 10 and Day(act.date) < 11, 1 , null)) as "10",
        count(if(Day(act.date) >= 11 and Day(act.date) < 12, 1 , null)) as "11",
        count(if(Day(act.date) >= 12 and Day(act.date) < 13, 1 , null)) as "12",
        count(if(Day(act.date) >= 13 and Day(act.date) < 14, 1 , null)) as "13",
        count(if(Day(act.date) >= 14 and Day(act.date) < 15, 1 , null)) as "14",
        count(if(Day(act.date) >= 15 and Day(act.date) < 16, 1 , null)) as "15",
        count(if(Day(act.date) >= 16 and Day(act.date) < 17, 1 , null)) as "16",
        count(if(Day(act.date) >= 17 and Day(act.date) < 18, 1 , null)) as "17",
        count(if(Day(act.date) >= 18 and Day(act.date) < 19, 1 , null)) as "18",
        count(if(Day(act.date) >= 19 and Day(act.date) < 20, 1 , null)) as "19",
        count(if(Day(act.date) >= 20 and Day(act.date) < 21, 1 , null)) as "20",
        count(if(Day(act.date) >= 21 and Day(act.date) < 22, 1 , null)) as "21",
        count(if(Day(act.date) >= 22 and Day(act.date) < 23, 1 , null)) as "22",
        count(if(Day(act.date) >= 23 and Day(act.date) < 24, 1 , null)) as "23",
        count(if(Day(act.date) >= 24 and Day(act.date) < 25, 1 , null)) as "24",
        count(if(Day(act.date) >= 25 and Day(act.date) < 26, 1 , null)) as "25",
        count(if(Day(act.date) >= 26 and Day(act.date) < 27, 1 , null)) as "26",
        count(if(Day(act.date) >= 27 and Day(act.date) < 28, 1 , null)) as "27",
        count(if(Day(act.date) >= 28 and Day(act.date) < 29, 1 , null)) as "28",
        count(if(Day(act.date) >= 29 and Day(act.date) < 30, 1 , null)) as "29",
        count(if(Day(act.date) >= 30 and Day(act.date) < 31, 1 , null)) as "30",
        count(if(Day(act.date) = 31, 1 , null)) as "31",
        count(distinct bug.id) as total
    from
        zt_action as act
    left join
        zt_bug as bug
    on
        bug.id=act.objectID
    left join
        zt_user as usr
    on
        usr.account=act.actor
    left join
        zt_user as usrB
    on
        usrB.account = bug.openedBy
    where
        act.action='edited'
    and
        objectType='bug'
    and
        bug.status = 'active'
    and
        act.comment != ''
        {
        '''AND
            act.date >= "%s" ''' % _date_bug_verify_start if _date_bug_verify_start else ""
        }
        {
        '''AND
            act.date < "%s" ''' % _date_bug_verify_end if _date_bug_verify_end else "" 
        }
    and
        bug.openedBy = act.actor
)

UNION

(
    select
        usr.realname as actor,
        count(if(Day(act.date) >= 1  and Day(act.date) < 2, 1 , null)) as "1",
        count(if(Day(act.date) >= 2  and Day(act.date) < 3, 1 , null)) as "2",
        count(if(Day(act.date) >= 3  and Day(act.date) < 4, 1 , null)) as "3",
        count(if(Day(act.date) >= 4  and Day(act.date) < 5, 1 , null)) as "4",
        count(if(Day(act.date) >= 5  and Day(act.date) < 6, 1 , null)) as "5",
        count(if(Day(act.date) >= 6  and Day(act.date) < 7, 1 , null)) as "6",
        count(if(Day(act.date) >= 7  and Day(act.date) < 8, 1 , null)) as "7",
        count(if(Day(act.date) >= 8  and Day(act.date) < 9, 1 , null)) as "8",
        count(if(Day(act.date) >= 9  and Day(act.date) < 10, 1 , null)) as "9",
        count(if(Day(act.date) >= 10 and Day(act.date) < 11, 1 , null)) as "10",
        count(if(Day(act.date) >= 11 and Day(act.date) < 12, 1 , null)) as "11",
        count(if(Day(act.date) >= 12 and Day(act.date) < 13, 1 , null)) as "12",
        count(if(Day(act.date) >= 13 and Day(act.date) < 14, 1 , null)) as "13",
        count(if(Day(act.date) >= 14 and Day(act.date) < 15, 1 , null)) as "14",
        count(if(Day(act.date) >= 15 and Day(act.date) < 16, 1 , null)) as "15",
        count(if(Day(act.date) >= 16 and Day(act.date) < 17, 1 , null)) as "16",
        count(if(Day(act.date) >= 17 and Day(act.date) < 18, 1 , null)) as "17",
        count(if(Day(act.date) >= 18 and Day(act.date) < 19, 1 , null)) as "18",
        count(if(Day(act.date) >= 19 and Day(act.date) < 20, 1 , null)) as "19",
        count(if(Day(act.date) >= 20 and Day(act.date) < 21, 1 , null)) as "20",
        count(if(Day(act.date) >= 21 and Day(act.date) < 22, 1 , null)) as "21",
        count(if(Day(act.date) >= 22 and Day(act.date) < 23, 1 , null)) as "22",
        count(if(Day(act.date) >= 23 and Day(act.date) < 24, 1 , null)) as "23",
        count(if(Day(act.date) >= 24 and Day(act.date) < 25, 1 , null)) as "24",
        count(if(Day(act.date) >= 25 and Day(act.date) < 26, 1 , null)) as "25",
        count(if(Day(act.date) >= 26 and Day(act.date) < 27, 1 , null)) as "26",
        count(if(Day(act.date) >= 27 and Day(act.date) < 28, 1 , null)) as "27",
        count(if(Day(act.date) >= 28 and Day(act.date) < 29, 1 , null)) as "28",
        count(if(Day(act.date) >= 29 and Day(act.date) < 30, 1 , null)) as "29",
        count(if(Day(act.date) >= 30 and Day(act.date) < 31, 1 , null)) as "30",
        count(if(Day(act.date) = 31, 1 , null)) as "31",
        count(distinct bug.id) as total
    from
        zt_action as act
    left join
        zt_bug as bug
    on
        bug.id=act.objectID
    left join
        zt_user as usr
    on
        usr.account=act.actor
    left join
        zt_user as usrB
    on
        usrB.account = bug.openedBy
    where
        act.action='edited'
    and
        objectType='bug'
    and
        bug.status = 'active'
    and
        act.comment != ''
        {
        '''AND
            act.date >= "%s" ''' % _date_bug_verify_start if _date_bug_verify_start else ""
        }
        {
        '''AND
            act.date < "%s" ''' % _date_bug_verify_end if _date_bug_verify_end else "" 
        }
    and
        bug.openedBy = act.actor
    group by
        act.actor
)
"""


def construct_sql_Bug_close_all_with_detail_by_date(
        _date_bug_close_start,
        _date_bug_close_end
):
    return f"""
(
    select
        "【总数】" as closer, 
        count(if(Day(bug.closedDate) >= 1  and Day(bug.closedDate) < 2, 1 , null)) as "1",
        count(if(Day(bug.closedDate) >= 2  and Day(bug.closedDate) < 3, 1 , null)) as "2",
        count(if(Day(bug.closedDate) >= 3  and Day(bug.closedDate) < 4, 1 , null)) as "3",
        count(if(Day(bug.closedDate) >= 4  and Day(bug.closedDate) < 5, 1 , null)) as "4",
        count(if(Day(bug.closedDate) >= 5  and Day(bug.closedDate) < 6, 1 , null)) as "5",
        count(if(Day(bug.closedDate) >= 6  and Day(bug.closedDate) < 7, 1 , null)) as "6",
        count(if(Day(bug.closedDate) >= 7  and Day(bug.closedDate) < 8, 1 , null)) as "7",
        count(if(Day(bug.closedDate) >= 8  and Day(bug.closedDate) < 9, 1 , null)) as "8",
        count(if(Day(bug.closedDate) >= 9  and Day(bug.closedDate) < 10, 1 , null)) as "9",
        count(if(Day(bug.closedDate) >= 10 and Day(bug.closedDate) < 11, 1 , null)) as "10",
        count(if(Day(bug.closedDate) >= 11 and Day(bug.closedDate) < 12, 1 , null)) as "11",
        count(if(Day(bug.closedDate) >= 12 and Day(bug.closedDate) < 13, 1 , null)) as "12",
        count(if(Day(bug.closedDate) >= 13 and Day(bug.closedDate) < 14, 1 , null)) as "13",
        count(if(Day(bug.closedDate) >= 14 and Day(bug.closedDate) < 15, 1 , null)) as "14",
        count(if(Day(bug.closedDate) >= 15 and Day(bug.closedDate) < 16, 1 , null)) as "15",
        count(if(Day(bug.closedDate) >= 16 and Day(bug.closedDate) < 17, 1 , null)) as "16",
        count(if(Day(bug.closedDate) >= 17 and Day(bug.closedDate) < 18, 1 , null)) as "17",
        count(if(Day(bug.closedDate) >= 18 and Day(bug.closedDate) < 19, 1 , null)) as "18",
        count(if(Day(bug.closedDate) >= 19 and Day(bug.closedDate) < 20, 1 , null)) as "19",
        count(if(Day(bug.closedDate) >= 20 and Day(bug.closedDate) < 21, 1 , null)) as "20",
        count(if(Day(bug.closedDate) >= 21 and Day(bug.closedDate) < 22, 1 , null)) as "21",
        count(if(Day(bug.closedDate) >= 22 and Day(bug.closedDate) < 23, 1 , null)) as "22",
        count(if(Day(bug.closedDate) >= 23 and Day(bug.closedDate) < 24, 1 , null)) as "23",
        count(if(Day(bug.closedDate) >= 24 and Day(bug.closedDate) < 25, 1 , null)) as "24",
        count(if(Day(bug.closedDate) >= 25 and Day(bug.closedDate) < 26, 1 , null)) as "25",
        count(if(Day(bug.closedDate) >= 26 and Day(bug.closedDate) < 27, 1 , null)) as "26",
        count(if(Day(bug.closedDate) >= 27 and Day(bug.closedDate) < 28, 1 , null)) as "27",
        count(if(Day(bug.closedDate) >= 28 and Day(bug.closedDate) < 29, 1 , null)) as "28",
        count(if(Day(bug.closedDate) >= 29 and Day(bug.closedDate) < 30, 1 , null)) as "29",
        count(if(Day(bug.closedDate) >= 30 and Day(bug.closedDate) < 31, 1 , null)) as "30",
        count(if(Day(bug.closedDate) = 31, 1 , null)) as "31", 
        count(bug.id) as total 
    FROM
        zt_bug as bug 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=bug.project
    LEFT JOIN
        zt_project as pject 
    ON
        pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,",",2),",",-1) 
    LEFT JOIN
        zt_user as usr
    ON
        usr.account=bug.openedBy 
    LEFT JOIN
        zt_user as usrB 
    ON
        usrB.account=bug.assignedTo 
    WHERE
        bug.deleted="0"
    and 
        bug.status='closed' 
    {
    '''AND
        bug.closedDate >= "%s" ''' % _date_bug_close_start if _date_bug_close_start else ""
    }
    {
    '''AND
        bug.closedDate < "%s" ''' % _date_bug_close_end if _date_bug_close_end else "" 
    }
)

UNION

(
    SELECT
        usr.realname as closer,
        count(if(Day(bug.closedDate) >= 1  and Day(bug.closedDate) < 2, 1 , null)) as "1",
        count(if(Day(bug.closedDate) >= 2  and Day(bug.closedDate) < 3, 1 , null)) as "2",
        count(if(Day(bug.closedDate) >= 3  and Day(bug.closedDate) < 4, 1 , null)) as "3",
        count(if(Day(bug.closedDate) >= 4  and Day(bug.closedDate) < 5, 1 , null)) as "4",
        count(if(Day(bug.closedDate) >= 5  and Day(bug.closedDate) < 6, 1 , null)) as "5",
        count(if(Day(bug.closedDate) >= 6  and Day(bug.closedDate) < 7, 1 , null)) as "6",
        count(if(Day(bug.closedDate) >= 7  and Day(bug.closedDate) < 8, 1 , null)) as "7",
        count(if(Day(bug.closedDate) >= 8  and Day(bug.closedDate) < 9, 1 , null)) as "8",
        count(if(Day(bug.closedDate) >= 9  and Day(bug.closedDate) < 10, 1 , null)) as "9",
        count(if(Day(bug.closedDate) >= 10 and Day(bug.closedDate) < 11, 1 , null)) as "10",
        count(if(Day(bug.closedDate) >= 11 and Day(bug.closedDate) < 12, 1 , null)) as "11",
        count(if(Day(bug.closedDate) >= 12 and Day(bug.closedDate) < 13, 1 , null)) as "12",
        count(if(Day(bug.closedDate) >= 13 and Day(bug.closedDate) < 14, 1 , null)) as "13",
        count(if(Day(bug.closedDate) >= 14 and Day(bug.closedDate) < 15, 1 , null)) as "14",
        count(if(Day(bug.closedDate) >= 15 and Day(bug.closedDate) < 16, 1 , null)) as "15",
        count(if(Day(bug.closedDate) >= 16 and Day(bug.closedDate) < 17, 1 , null)) as "16",
        count(if(Day(bug.closedDate) >= 17 and Day(bug.closedDate) < 18, 1 , null)) as "17",
        count(if(Day(bug.closedDate) >= 18 and Day(bug.closedDate) < 19, 1 , null)) as "18",
        count(if(Day(bug.closedDate) >= 19 and Day(bug.closedDate) < 20, 1 , null)) as "19",
        count(if(Day(bug.closedDate) >= 20 and Day(bug.closedDate) < 21, 1 , null)) as "20",
        count(if(Day(bug.closedDate) >= 21 and Day(bug.closedDate) < 22, 1 , null)) as "21",
        count(if(Day(bug.closedDate) >= 22 and Day(bug.closedDate) < 23, 1 , null)) as "22",
        count(if(Day(bug.closedDate) >= 23 and Day(bug.closedDate) < 24, 1 , null)) as "23",
        count(if(Day(bug.closedDate) >= 24 and Day(bug.closedDate) < 25, 1 , null)) as "24",
        count(if(Day(bug.closedDate) >= 25 and Day(bug.closedDate) < 26, 1 , null)) as "25",
        count(if(Day(bug.closedDate) >= 26 and Day(bug.closedDate) < 27, 1 , null)) as "26",
        count(if(Day(bug.closedDate) >= 27 and Day(bug.closedDate) < 28, 1 , null)) as "27",
        count(if(Day(bug.closedDate) >= 28 and Day(bug.closedDate) < 29, 1 , null)) as "28",
        count(if(Day(bug.closedDate) >= 29 and Day(bug.closedDate) < 30, 1 , null)) as "29",
        count(if(Day(bug.closedDate) >= 30 and Day(bug.closedDate) < 31, 1 , null)) as "30",
        count(if(Day(bug.closedDate) = 31, 1 , null)) as "31", 
        count(bug.id) as total
    FROM
        zt_bug as bug 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id = bug.project 
    LEFT JOIN
        zt_project as pject 
    ON
        pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path, ',', 2),',', -1)
    LEFT JOIN
        zt_user as usr 
    ON
        usr.account = bug.closedBy
    WHERE
        bug.deleted='0'
    and 
        bug.status='closed'
    {
    '''AND
        bug.closedDate >= "%s" ''' % _date_bug_close_start if _date_bug_close_start else ""
    }
    {
    '''AND
        bug.closedDate < "%s" ''' % _date_bug_close_end if _date_bug_close_end else "" 
    }
    GROUP BY
        bug.closedBy
)
"""
































